This invention relates to acquisition of performance and resource consumption data from a relational database, and, more particularly, to a method of retrieving this data, specific to Structured Query Language (SQL) queries, from the DB2 database licensed by International Business Machines Corp. (IBM). The American National Standards Institute (ANSI) and the International Standards Organization (ISO) published SQL standards in 1986 and 1987, respectively. ANSI and ISO jointly worked on an extension to the standards, variously called SQL2, SQL-92, or SQL/92. Another extension to the standards, SQL3, is in progress, to enhance relational capabilities, and add support for object-oriented features.
A query to a relational database is a request for information from the database based on specific conditions. For example, a query could be a request for a list of all customers in a customer table who live in Texas. In order to reduce response time to SQL queries which are directed to a relational database, a user, such as a systems programer, an applications programer, or a database administrator, improves his hardware, by buying a faster CPU or adding more space for data storage, or both, or improving his response time by the buffering of data objects.
Alternatively, the user optimizes the SQL statements. The user optimizes SQL statements, also known as "tuning", through monitoring and measuring SQL performance. He measures performance by determining what amount of available system resources an individual SQL statement is using, and the overall workload impact of any specific SQL statement. The user makes changes based on these observations.
However, due to the complexity of the relationships among the data, the process of tuning SQL statements is complex, time consuming, and demands considerable system resources. For example, DB2 for Multiple Virtual System (MVS) offers a detailed SQL trace capability. However, this SQL trace can add anywhere from 50% to 100% overhead to DB2. Because of this large overhead, the SQL trace is generally not continually used. Other optimization programs, such as thread or transaction level traces, provide measures that identify only the most resource-demanding groups of SQL statements. These traces are not capable of identifying the specific SQL statements in need of tuning. Still other optimization routines measure performance across the entire SQL process, including DB2's SQL Processing Prologue and the Epilogue. These routines give an aggregate measure called "Class 2" time, which is of limited value in determining how a specific SQL statement performed.
SQL Data Manipulation Language (DML) statements represent the actual low-level SQL queries which either select, update, insert, or delete data from DB2. Optimizing these on a statement by statement basis is the most effective way to minimize resource consumption and maximize performance.
What the industry needs is a method of reducing the response time to SQL queries directed to a relational database, without having to increase the hardware requirements, and without having to provide more memory caches for data objects. More specifically, what the industry needs is a program that will directly monitor SQL DML performance and resource consumption on an ongoing basis, without the monitoring program consuming 50% or more of system resources.